﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Web;
using HRM.DAL.DAO;

namespace HRM.DAL.Gateway
{
    public class ContactGateway : DBGateway
    {
        public int HasContactInfoAlreadyExists(Contact contact)
        {
            int empId = 0;
            try
            {
                ConnectionObj.Open();
                CommandObj.CommandText = @"SELECT tblContacts.EmployeeID FROM tblContacts INNER JOIN tblEmployees ON tblContacts.EmployeeID = tblEmployees.EmployeeId INNER JOIN
                      tblCode ON tblEmployees.Code_CodeID = tblCode.Id WHERE tblCode.CodeName = '" + contact.Employee.EmpCode + "'";
                SqlDataReader reader = CommandObj.ExecuteReader();
                while (reader.Read())
                {
                    empId = Convert.ToInt32(reader[0].ToString());
                }
            }
            catch (Exception)
            {
                throw;
            }
            finally
            {
                ConnectionObj.Close();
            }
            return empId;
        }

        public string SaveContact(Contact contact)
        {
            try
            {
                EmployeeGateway empGateway = new EmployeeGateway();
                int empId = empGateway.GetEmployeeID(contact.Employee.EmpCode);
                ConnectionObj.Open();
                CommandObj.CommandText = string.Format("insert into tblContacts values(@empId,@fcon,@scon,@ecp,@ecn,@pread,@perad)");
                CommandObj.Parameters.Clear();
                CommandObj.Parameters.Add("@empId", SqlDbType.Int).Value = empId;
                CommandObj.Parameters.Add("@fcon", SqlDbType.VarChar).Value = contact.FirstContact;
                CommandObj.Parameters.Add("@scon", SqlDbType.VarChar).Value = contact.Secondcontact;
                CommandObj.Parameters.Add("@ecp", SqlDbType.VarChar).Value = contact.EmergencyContactPerson;
                CommandObj.Parameters.Add("@ecn", SqlDbType.VarChar).Value = contact.EmergencyContactNumber;
                CommandObj.Parameters.Add("@pread", SqlDbType.VarChar).Value = contact.PresentAddress;
                CommandObj.Parameters.Add("@perad", SqlDbType.VarChar).Value = contact.PermanentAddress;
                CommandObj.ExecuteNonQuery();
                return "Contact information Successfully Saved";
            }
            catch (Exception)
            {
                throw new Exception("Information Not Save, Please check inputs & try again !");
            }
            finally
            {
                ConnectionObj.Close();
            }
        }

        public string UpdateContact(int empId, Contact contact)
        {
            try
            {
                ConnectionObj.Open();
                CommandObj.CommandText = string.Format("update tblContacts set EmployeeID=@empId,FirstContact=@fcon,Secondcontact=@scon,EmergencyContactPerson=@ecp," +
                                                       "EmergencyContactNumber=@ecn,PresentAddress=@pread,PermanentAddress=@perad");
                CommandObj.Parameters.Clear();
                CommandObj.Parameters.Add("@empId", SqlDbType.Int).Value = empId;
                CommandObj.Parameters.Add("@fcon", SqlDbType.VarChar).Value = contact.FirstContact;
                CommandObj.Parameters.Add("@scon", SqlDbType.VarChar).Value = contact.Secondcontact;
                CommandObj.Parameters.Add("@ecp", SqlDbType.VarChar).Value = contact.EmergencyContactPerson;
                CommandObj.Parameters.Add("@ecn", SqlDbType.VarChar).Value = contact.EmergencyContactNumber;
                CommandObj.Parameters.Add("@pread", SqlDbType.VarChar).Value = contact.PresentAddress;
                CommandObj.Parameters.Add("@perad", SqlDbType.VarChar).Value = contact.PermanentAddress;
                CommandObj.ExecuteNonQuery();
                return "Contact information updated Successfully";
            }
            catch (Exception)
            {
                throw new Exception("Information Not Update, Please check inputs & try again !");
            }
            finally
            {
                ConnectionObj.Close();
            }
        }

        public Contact GetEmployeeContactInfo(Contact contact)
        {
           try
            {
                EmployeeGateway empGateway = new EmployeeGateway();
                int empId = empGateway.GetEmployeeID(contact.Employee.EmpCode);
                ConnectionObj.Open();
                CommandObj.CommandText = @"select * from tblContacts where EmployeeID='" + empId + "'";
                SqlDataReader reader = CommandObj.ExecuteReader();
                Contact contactInfo=new Contact();
                while (reader.Read())
                {
                    contactInfo.FirstContact=reader[2].ToString();
                    contactInfo.Secondcontact = reader[3].ToString();
                    contactInfo.EmergencyContactPerson = reader[4].ToString();
                    contactInfo.EmergencyContactNumber = reader[5].ToString();
                    contactInfo.PresentAddress = reader[6].ToString();
                    contactInfo.PermanentAddress = reader[7].ToString();
                }
                return contactInfo;
            }
            catch (Exception)
            {
                throw;
            }
            finally
            {
                ConnectionObj.Close();
            } 
        }


        public List<Contact> GetAllEmployeesContactInfo(Contact contact)
        {
            try
            {
                EmployeeGateway empGateway = new EmployeeGateway();
                int empId = empGateway.GetEmployeeID(contact.Employee.EmpCode);
                ConnectionObj.Open();
                CommandObj.CommandText = @"slect * from tblContacts where EmployeeID='" + empId + "'";
                SqlDataReader reader = CommandObj.ExecuteReader();
                Contact contactInfo = new Contact();
                List<Contact>contacts=new List<Contact>();
                while (reader.Read())
                {
                    contactInfo.FirstContact = reader[2].ToString();
                    contactInfo.Secondcontact = reader[3].ToString();
                    contactInfo.EmergencyContactPerson = reader[4].ToString();
                    contactInfo.EmergencyContactNumber = reader[5].ToString();
                    contactInfo.PresentAddress = reader[6].ToString();
                    contactInfo.PermanentAddress = reader[7].ToString();
                    contacts.Add(contact);
                }
                return contacts;
            }
            catch (Exception)
            {
                throw;
            }
            finally
            {
                ConnectionObj.Close();
            }
        }
    }
}